<?php
/**
 * Created by PhpStorm.
 * User: user4
 * Date: 2018/11/26
 * Time: 10:04
 */
function connect(){
	$servername="localhost";
	$username="root";
	$password="";
	$conn = mysqli_connect($servername, $username, $password);
	if(!$conn){
		die('failed'.$conn->connect_errno);
	}
	else {echo "connect db sucessfully\n";}
	return $conn;
}

function build_db($conn,$name){
	$sql = "CREATE DATABASE IF NOT EXISTS $name";
	if(mysqli_select_db($conn,"$name"))echo "exited\n";
	if($conn->query($sql)===true)echo "build db suceeded\n";
	else echo "failed:".$conn->error."\n";
}

function get_all_dbs(){
	$dbs = mysql_list_dbs();
	while($db=mysqli_fetch_row($dbs)){
		echo $db."\n";
	}
}
function select_db($name,$conn){
	$sql="use $name";
	if($conn->query($sql)){
		echo "select db $name sucessfully\n";
	}
	else {
		echo "failed to select db $name";
	}
}
function build_table_students($conn){
	$sql='CREATE TABLE if not exists Students (
  		id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
  		name VARCHAR(10),
  		sex VARCHAR(10),
  		age TINYINT(3),
  		birth DATE
	)';
	if($conn->query($sql)===true)echo "build Students table sucessfully\n";
	else echo "build stu table failed:$conn->error";
}
function build_table_subjects($conn){
	$sql='CREATE table if not exists Subjects(
		id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
		name VARCHAR(10),
		first_learning VARCHAR(10)
	)';
	if($conn->query($sql)===true)echo "build Subjects table sucessfully\n";
	else echo "build sub table failed:$conn->error";
}
function build_table_score($conn){
	$sql='CREATE table if not exists choosing_sub(
		subject_id INT(6),
		student_id Int(10),
		score INT(10),
		FOREIGN KEY (subject_id) REFERENCES Subjects(id),
		FOREIGN KEY (student_id) REFERENCES Students(id)
	)';
	if($conn->query($sql)===true)echo "build choosing_sub table sucessfully\n";
	else echo "build choosing table failed:$conn->error";
}
$href=connect();
build_db($href,"Teaching_mess");
//get_all_dbs();
select_db("Teaching_mess",$href);
build_table_students($href);
build_table_subjects($href);
build_table_score($href);

// mysqli_select_db($conn,"Teaching mess");
// $sql = 'CREATE TABLE IF NOT EXISTS Students (
//   id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
//   namee VARCHAR(10) ,
//   sex VARCHAR(10),
//   age TINYINT(3),
//   birth DATE()
// )';
// if ($conn->query($sql)){
// 	echo "build table sucessfully\n";
// }
// else{
// 	echo "build table failed:".$conn->error."\n";
// }
// $sql = "INSERT INTO Students (id, namee, sex,age,birth)
// VALUES (0, 'cz', 'femfal',20,'2019-1-1');";
// $sql .= "INSERT INTO Students (id, namee, sex,age,birth)
// VALUES (0, 'cz', 'femfal',20,'2019-1-1');";
// $sql .= "INSERT INTO Students (id, namee, sex,age,birth)
// VALUES (0, 'cz', 'femfal',20,'2019-1-1');";
// mysqli_select_db($conn,"Teaching mess");
// if($conn->query($sql)){
//     echo "inserting suceeded\n";
// }
// else{
//     echo "inserting failed\n".$conn->error;
// }
// $sql = "SELECT id, name, sex,age,birth FROM Students WHERE age>=18 and age <=22";
// $result=$conn->query($sql);
// if($result->num_rows>0){
//     while($row=$result->fetch_assoc()){
//         echo $row['id']." ".$row['name']." ".$row['sex']." ".$row['age']." ".$row['birth']."\n";
//     }
// }
// else{
//     echo "0 results has been found...";
// }
// $sql="UPDATE Students SET id=81 where id=0";
// $conn->close();
// ?>